/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package construtora.limiar.dao.generic;

import com.mysql.jdbc.Connection;
import com.mysql.jdbc.Statement;
import java.io.InputStream;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;
import construtora.limiar.mensagens.Message;
/**
 *
 * @author michel
 */
public class DAOEngine {
    
    private static String url;
    private static String username;
    private static String password;
    private static String driver;
    private static Connection connection = null;
    private static final byte NAME = 0;
    private static final byte VALUE = 1;
    private static Statement statement = null;
    
    private static final String ERROR_NO_CONNECTION = "Não conseguiu estabelecer conexão com o banco de dados";

    public DAOEngine(String username, String password) {
        this.username = username;
        this.password = password;
    }
    
    
    public static String getLastException() {
        return Message.getLastException();
    }
    
    /*
    private static boolean openConnnection() {
        InputStream in = new DAOEngine().getClass().getClassLoader().getResourceAsStream("connection.properties");
        Properties properties = new Properties();
        try {
            properties.load(in);
            in.close();
            driver = properties.getProperty("driver");
            url = properties.getProperty("url");
            username = properties.getProperty("username");
            password = properties.getProperty("password");
        } catch (Exception e) {
            driver = "com.mysql.jdbc.Driver";
            url = "jdbc:mysql://localhost:3306/projlimiar";
            username = "root";
            password = "   ";
        }
        try {
            Class.forName(driver);
            connection = (Connection) DriverManager.getConnection(url, username, password);
            return true;
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;

    }
    
    */
    
    public static boolean openConnnection() {
        driver = "com.mysql.jdbc.Driver";
        url = "jdbc:mysql://localhost:3306/projlimiar";
        try {
            Class.forName(driver);
            connection = (Connection) DriverManager.getConnection(url, username, password);
            return true;
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;

        
    }
    private static boolean isConnected() {
        return (connection != null);
    }
    
    public static boolean insert(String table, String[][] fields) {
        String names = "";
        String values = "";
        String sql = "INSERT INTO " + table + "(";
        for (int i = 0; i < fields.length; i++) {
            names = names + fields[i][NAME] + ",";
            values = values + fields[i][VALUE] + ",";
        }
        if (names.substring(names.length() - 1).equals(","))
            names = names.substring(0, names.length() - 1);
        if (values.substring(values.length() - 1).equals(","))
            values = values.substring(0, values.length() - 1);
        sql = sql + names + ") VALUES (" + values + ")";
        return executeSQL(sql);
    }
    
    public static boolean executeSQL(String sql) {
        if (!connectionEstablished())
            return false;
        try {
            statement = (Statement) connection.createStatement();
            boolean result = statement.execute(sql);
            //connection.commit(); ///////////////////AQUI
            statement.close();
            //connection.close();
            //connection = null;
            //statement.executeQuery(sql);
            return result;
        } catch (SQLException e) {
            connection = null;
        }
            return false;
    }
    
    public static boolean update(String table, String[][] fields, String where) {
        String settings = "";
        String sql = "UPDATE " + table + " SET ";
        for (int i = 0; i < fields.length; i++) {
            settings = settings + fields[i][NAME] + " = " + fields[i][VALUE] + ",";
        }
        if (settings.substring(settings.length() - 1).equals(","))
            settings = settings.substring(0, settings.length() - 1);
        sql = sql + settings + " where " + where;
        return executeSQL(sql);
    }
    
    public static boolean delete(String table, String where) {
        String sql = "DELETE FROM " + table + "";
        sql = sql + " where " + where;
        return executeSQL(sql);
    }
    
    public static ResultSet fetchAll(String table, String where, String order, Integer limit, Integer offset) {
        String sql;
       /* if (connection != null)
            try {
                connection.close();
                connection = null;
            } catch (SQLException e1) {
                connection = null;
            }
       */
        //if (!openConnnection())
            //return null;
        if (!connectionEstablished())
            return null;
        sql = "SELECT * FROM " + table;
        sql = sql + (where == null ? "" : " WHERE " + where);
        sql = sql + (order == null ? "" : " ORDER BY " + order);
        sql = sql + (limit == null ? "" : " LIMIT " + limit);
        sql = sql + (offset == null ? "" : " OFFSET " + offset);
        try {
            statement = (Statement) connection.createStatement();
            ResultSet rs = statement.executeQuery(sql);
            return rs;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }
    
    private static boolean connectionEstablished() {
        if (!isConnected())
            if (!openConnnection()) {
                Message.setLastException(ERROR_NO_CONNECTION);
                return false;
            }
            return true;
    }
    
    public static Integer lastValue(String table, String field) {
        String sql;
        if (!connectionEstablished())
            return null;
        sql = "SELECT * FROM " + table + " ORDER BY " + field + " DESC";
        try {
            statement = (Statement) connection.createStatement();
            ResultSet rs = statement.executeQuery(sql);
            if (rs.next()) {
                return rs.getInt(field);
            }
        } catch (SQLException e) {
            Message.setLastException(e.getMessage());
        }
        return null;
    }
    
}
